import requests
from fake_useragent import UserAgent
from lxml import etree
import pymysql
url='https://datachart.500.com/ssq/'
# 提取数据
headers={
    "User-Agent":UserAgent().chrome
}
response =requests.get(url,headers=headers)
e=etree.HTML(response.text)
data_times=e.xpath('//tbody[@id="tdata"]/tr/td[@align="center"]/text()')
trs=e.xpath('//tbody[@id="tdata"]/tr[not(@class)]')
# 连接数据库
client=pymysql.connect(host='localhost',port=3306,user='root',password='root',charset='utf8',db='double_color_ball')
cursor=client.cursor()
sql='insert into t_ball values(0,%s,%s,%s)'
select_sql='select * from t_ball where data_time=%s'
data_times.reverse()
# 记录有多少条新数据
index=0
for data_time in data_times:
    result=cursor.execute(select_sql,[data_time])
    if result==1:
        break
    index+=1
print(index)
trs.reverse()
# for data_time,tr in zip(data_times,trs):
for i in range(index):
    red_ball='-'.join(trs[i].xpath('./td[@class="chartBall01"]/text()'))
    blue_ball=trs[i].xpath('./td[@class="chartBall02"]/text()')[0]
    print("第"+data_times[i]+"期:红球是{"+red_ball+"} 蓝球是"+blue_ball)
    cursor.execute(sql,[data_times[i],red_ball,blue_ball])
    client.commit()
cursor.close()
client.close()









